﻿'Description:去封/集件
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-05-16
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient

Public Class SealAndIssueBusiness
    Private m_ContainerObj As New uLMSContainerBusiness
    ''' <summary>
    ''' 获取去封、集件--任务单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectContainerNo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                SELECT wf.DESCRIPTION wfdesc,NVL(e.containerid,e.enginecontainerid) containerid,cc.mfgordername,cc.productname,cc.PRODUCTDESCRIPTION description,e.workflowid,e.mfgorderid, 
                 e.containerno togetherflag,decode(e."TYPE",'2','临时','3','临时','正常') WORKFLOWTYPE,decode(e.containerid,null,'未生成','已生成') TaskBillStatus,
                 wfs.workflowstepname,to_char(e.plannedstartdate,'yyyy-MM-dd HH:mi') plannedstartdate, to_char(e.plannedfinishdate,'yyyy-MM-dd HH:mi') plannedfinishdate
                ,NVL(MTFULLSETRATE,0) QTL,0 pmCOUNT,0 FINISHCOUNT
                 FROM enginepartplan e 
                 left JOIN workflowstep wfs ON e.workflowstepid=wfs.workflowstepid
                 LEFT JOIN workflow wf ON wf.workflowid=e.workflowid
                 left join containercurrentinfo cc on cc.containerid=nvl(e.containerid,e.enginecontainerid)
                 LEFT JOIN bom b on b.workflowid=e.workflowid
                 left join billtype bt on b.billtypeid=bt.billtypeid 
                </string>)
        '       LEFT JOIN (
        '       	 SELECT COUNt(1) pmCOUNT,SUM(CASE WHEN totalCollectQty>=firstassembleqty THEN 1 ELSE 0 END) FINISHCOUNT,ContainerID FROM (
        'SELECT iss.ContainerID,SUM(pm.FIRSTASSEMBLEQTY)firstassembleqty,pm.PRODUCTID ,con.confirmqty totalCollectQty 
        '               FROM ( select SUM(FIRSTASSEMBLEQTY) FIRSTASSEMBLEQTY,PARTMENUINFOID,PRODUCTID from partmenuinfo group by PARTMENUINFOID,PRODUCTID) pm
        'LEFT JOIN issueerpmaterial iss ON iss.PARTMENUINFOID=pm.PARTMENUINFOID
        'LEFT JOIN (SELECT SUM(NVL(confirmqty,0)) confirmqty,COLLECTERPMATERIALID,ContainerID FROM COLLECTERPMATERIALCONFIRM GROUP BY COLLECTERPMATERIALID,ContainerID) con ON con.COLLECTERPMATERIALID=iss.COLLECTERPMATERIALID AND con.ContainerID=iss.CONTAINERID 
        'where iss.issueqty>0   
        'GROUP BY iss.ContainerID,pm.PRODUCTID ,con.confirmqty
        ' ) GROUP BY ContainerID
        '       ) pp ON pp.ContainerID=NVL(e.containerid,e.enginecontainerid) 


        sb.AppendLine(" WHERE NVL(e.containerid,e.enginecontainerid)>'0' ") 'modify by longb 2023-10-16
        'add by longb 2024-04-10
        If para.ContainsKey("usecompleted") Then
            If Not String.IsNullOrWhiteSpace(para("usecompleted")) Then
                sb.AppendFormat(" and e.usecompleted='{0}' ", para("usecompleted"))
            End If
        End If
        If para.ContainsKey("HasBom") Then
            If para("HasBom") = "1" Then
                sb.AppendLine(" and bt.billtypename='大小件目录' ")
            Else
                sb.AppendLine(" and bt.billtypename<>'大小件目录'")
            End If
            'Else
            '    sb.AppendLine(" AND b.bomid >'0' and INSTR(b.billtypename,'大小件目录')>0 ")
        End If
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and e.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("PartType") Then
            If Not String.IsNullOrWhiteSpace(para("PartType")) Then
                sb.AppendFormat(" and pm.parttype='{0}' ", para("PartType"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and e.CONTAINERNO  like '%{0}%' ", para("Machine"))
            End If
        End If

        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and cc.mfgordername like '%{0}%' ", para("MfgOrdername"))
            End If
        End If

        If para.ContainsKey("WorkFlowType") Then
            If Not String.IsNullOrWhiteSpace(para("WorkFlowType")) Then
                sb.AppendFormat(" and decode(e.TYPE,'2','0','3','0','1')='{0}' ", para("WorkFlowType"))
            End If
        End If

        'If para.ContainsKey("Status") Then
        '    If para("Status") = "0" Then
        '        sb.AppendLine(" AND decode(e.containerid,null,'未生成','已生成')='未生成' ")
        '    ElseIf para("Status") = "1" Then
        '        sb.AppendLine(" AND decode(e.containerid,null,'未生成','已生成')='已生成' ")
        '    Else

        '    End If
        'End If

        If para.ContainsKey("QJ") Then
            sb.AppendLine(" AND NVL(MTFULLSETRATE,0)<1")
            'sb.AppendLine(" AND NVL(pmCOUNT,0)<>NVL(FINISHCOUNT,0)")
        End If
        sb.AppendLine("Order by e.plannedfinishdate,wfs.sequence,cc.mfgordername,cc.productname ASC")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetCollectContainerNo = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取去封、集件大件信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectIssue(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2）
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT * FROM (SELECT c.togetherflag,c.containername, im.issueerpmaterialid, cm.productname,cm.productbatchno,{0} firstassembleqty,im.issueqty,nvl(SUM(imc.confirmqty),0) totalCollectQty ", qty)
        sb.AppendLine(", wfs.workflowstepname, u.attributevalue MachineName,c.containerid,wfs.workflowid ")
        sb.AppendFormat(" ,p.description productdesc,p1.description togetherdesc, (im.ISSUEQTY-nvl(SUM(imc.confirmqty),0)) ChangeQTY,nvl(con.CONFIRMQTY,0) CONFIRMQTY,nvl(imcc.CONFIRMQTY,0) IssTotal,nvl(con.CONFIRMQTY,0)-nvl(SUM(imc.confirmqty),0) editorQTY")
        sb.AppendLine(" FROM issueerpmaterial im ")
        sb.AppendLine(" LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid ")
        sb.AppendLine("LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid ")
        sb.AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
        sb.AppendLine("LEFT JOIN product p ON pm.productid=p.productid ")
        sb.AppendFormat("LEFT JOIN (select * from IssueERPMaterialConfirmation where mfgorderid='{0}') imc ON im.issueerpmaterialid=imc.issueerpmaterialid ", para("mfgorderid"))
        sb.AppendLine("left join container c ON c.containerid=im.containerid ")
        sb.AppendLine("LEFT JOIN product p1 ON c.productid=p1.productid ")
        sb.AppendFormat("LEFT JOIN mfgorder mfg ON mfg.mfgorderid=c.mfgorderid  ")
        sb.AppendFormat(" LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid ")
        sb.AppendFormat(" LEFT JOIN (SELECT nvl(SUM(CONFIRMQTY),0) CONFIRMQTY,COLLECTERPMATERIALID FROM COLLECTERPMATERIALCONFIRM where ContainerID='{0}' GROUP BY COLLECTERPMATERIALID) con ON con.COLLECTERPMATERIALID=cm.collecterpmaterialid ", para("ContainerID"))
        sb.AppendFormat(" LEFT JOIN (")
        sb.AppendFormat(" SELECT iss.PARTMENUINFOID,SUM(con.confirmqty) confirmqty FROM issueerpmaterial iss")
        sb.AppendFormat(" LEFT JOIN (select * from IssueERPMaterialConfirmation where mfgorderid='{0}') con ON con.ISSUEERPMATERIALID =iss.ISSUEERPMATERIALID", para("mfgorderid"))
        sb.AppendFormat(" GROUP BY iss.PARTMENUINFOID")
        sb.AppendFormat(" ) imcc ON imcc.PARTMENUINFOID=im.PARTMENUINFOID")
        sb.AppendLine(" where im.issueqty>0 and  c.status='1' and u.userattributename='发动机' ")
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("ContainerNoList") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNoList")) Then
                sb.AppendFormat(" and im.ContainerNo in ('{0}') ", para("ContainerNoList").Replace(",", "','"))
            End If
        End If

        If para.ContainsKey("PartType") Then
            If Not String.IsNullOrWhiteSpace(para("PartType")) Then
                sb.AppendFormat(" and pm.parttype='{0}' ", para("PartType"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
            End If
        End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and im.workflowstepid ='{0}' ", para("workflowstepid"))
            End If
        End If
        If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件
            sb.AppendLine(" AND nvl(pm.checkassembleqty,0)>0")
        End If
        sb.AppendFormat("GROUP BY wfs.workflowid,wfs.workflowstepname,c.containerid,u.attributevalue,c.togetherflag,c.containername, im.issueerpmaterialid,cm.productname,cm.productbatchno,{0},im.issueqty ", qty)
        sb.AppendLine(",p.description,p1.description,con.CONFIRMQTY,imcc.CONFIRMQTY ")
        sb.AppendLine("Order by u.attributevalue,c.togetherflag, cm.productname,cm.productbatchno,im.issueerpmaterialid asc )-- WHERE ChangeQTY>0")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' Add by longb20220517
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckContainerSteptimeSheet(ByVal para As Dictionary(Of String, String)) As Boolean
        CheckContainerSteptimeSheet = False
        '判断工时表资料是否存在
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT CS.* FROM containersteptimesheet CS ")
            .AppendLine("LEFT JOIN container c ON cs.containerid=c.containerid ")
            .AppendLine("LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid ")
            .AppendLine("LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid ")
            .AppendLine("WHERE u.userattributename='发动机' and c.status='1'  ")
            .AppendFormat(" AND u.attributevalue='{0}' AND cs.Stepinfoid='{1}' AND cs.Workflowstepid='{2}'", para("MachineName"), para("StepInfoId"), para("WorkFlowStepId"))
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            '发动机工时资料已经插入
            CheckContainerSteptimeSheet = True
        End If
    End Function

    ''' <summary>
    ''' Add by longb20220517
    ''' 获取集件去封工步信息
    ''' </summary>
    ''' <param name="WorkFlowId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectWorkFlowData(ByVal WorkFlowId As String, ByVal PartType As String) As DataTable
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT wfs.workflowid,wfs.workflowstepid,wfs.sequence,  si.Durationperunit , ")
            .AppendLine(" wfs.workflowstepname,sib.stepinfoname,si.Description,si.stepinfoid  FROM workflowstep wfs ")
            .AppendLine(" LEFT JOIN workflow wf ON wfs.workflowid=wf.workflowid ")
            .AppendLine(" LEFT JOIN stepinfo SI ON wfs.workflowstepid=si.Workflowstepid ")
            .AppendLine(" LEFT JOIN stepinfobase sib ON si.Stepinfobaseid=sib.stepinfobaseid ")
            .AppendFormat(" where wf.workflowid='{0}'  ", WorkFlowId)
            If PartType = "1" Then
                .AppendFormat(" and INSTR(si.Description,'集件')>0  ")
            Else
                .AppendFormat(" and INSTR(si.Description,'去封')>0 ")
            End If
            .AppendLine(" order by wfs.sequence asc ")
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
    ''' <summary>
    ''' Add by longb20220517
    ''' 获取对应工艺的工序信息表
    ''' </summary>
    ''' <param name="WorkFlowId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkFlowData(ByVal WorkFlowId As String) As DataTable
        Dim sb As New StringBuilder
        With sb
            .AppendFormat(" SELECT s.SPECID,SPECNO,OPDESC,WFS.WORKFLOWSTEPNAME,WFS.WORKFLOWSTEPID FROM WORKFLOWSTEP WFS")
            .AppendFormat(" LEFT JOIN Workflow wf ON wfs.workflowid = wf.workflowid")
            .AppendFormat(" LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid")
            .AppendFormat(" LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid)")
            .AppendFormat(" WHERE WFS.WORKFLOWID='{0}' ", WorkFlowId)
            .AppendLine(" ORDER BY  SPECNO  ")
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
    ''' <summary>
    ''' add by longb 查询工序下，去封/集件进度
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetProgressData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2） add by longb 2023-01-16
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT c.togetherflag,c.containername, im.issueerpmaterialid, cm.productname,cm.productbatchno,{0} firstassembleqty,im.issueqty,nvl(SUM(imc.confirmqty),0) totalCollectQty ", qty) 'modify by longb 2023-06-16
        sb.AppendLine(", wfs.workflowstepname, u.attributevalue MachineName,c.containerid,wfs.workflowid ")
        sb.AppendFormat(" ,p.description productdesc,p1.description togetherdesc, ({0}-nvl(SUM(imc.confirmqty),0)) ChangeQTY,nvl(con.CONFIRMQTY,0) CONFIRMQTY,nvl(imcc.CONFIRMQTY,0) IssTotal,pm.parttype", qty) 'modify by longb 2023-06-16
        sb.AppendLine(" FROM issueerpmaterial im ")
        sb.AppendLine(" LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid ")
        sb.AppendLine("LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid ")
        sb.AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
        sb.AppendLine("LEFT JOIN product p ON pm.productid=p.productid ")
        sb.AppendFormat("LEFT JOIN (select * from IssueERPMaterialConfirmation where mfgorderid='{0}') imc ON im.issueerpmaterialid=imc.issueerpmaterialid ", para("mfgorderid")) 'modify by longb 2023-06-16
        sb.AppendLine("left join container c ON c.containerid=im.containerid ")
        sb.AppendLine("LEFT JOIN product p1 ON c.productid=p1.productid ")
        sb.AppendFormat("LEFT JOIN mfgorder mfg ON mfg.mfgorderid=c.mfgorderid  ")
        sb.AppendFormat(" LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid ")
        sb.AppendFormat(" LEFT JOIN (SELECT nvl(SUM(CONFIRMQTY),0) CONFIRMQTY,COLLECTERPMATERIALID FROM COLLECTERPMATERIALCONFIRM GROUP BY COLLECTERPMATERIALID) con ON con.COLLECTERPMATERIALID=cm.collecterpmaterialid ")
        sb.AppendFormat(" LEFT JOIN (")
        sb.AppendFormat(" SELECT iss.PARTMENUINFOID,SUM(con.confirmqty) confirmqty FROM issueerpmaterial iss")
        sb.AppendFormat(" LEFT JOIN (select * from IssueERPMaterialConfirmation where mfgorderid='{0}') con ON con.ISSUEERPMATERIALID =iss.ISSUEERPMATERIALID", para("mfgorderid")) 'modify by longb 2023-06-16
        sb.AppendFormat(" GROUP BY iss.PARTMENUINFOID")
        sb.AppendFormat(" ) imcc ON imcc.PARTMENUINFOID=im.PARTMENUINFOID")
        sb.AppendLine(" where im.issueqty>0 and  c.status='1' and u.userattributename='发动机' ")
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If

        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and im.workflowstepid ='{0}' ", para("workflowstepid"))
            End If
        End If
        If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件 add by longb 2023-01-16
            sb.AppendLine(" AND nvl(pm.checkassembleqty,0)>0")
        End If
        sb.AppendFormat("GROUP BY wfs.workflowid,wfs.workflowstepname,c.containerid,u.attributevalue,c.togetherflag,c.containername, im.issueerpmaterialid,cm.productname,cm.productbatchno,{0},im.issueqty ", qty)
        sb.AppendLine(",p.description,p1.description,con.CONFIRMQTY,imcc.CONFIRMQTY,pm.parttype ")
        sb.AppendLine("Order by u.attributevalue,c.togetherflag, cm.productname,cm.productbatchno,im.issueerpmaterialid asc ")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' (分页)查询任务单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTempContainerNo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                            SELECT e.EnginepartplanID,wf.description wfdesc,u.attributevalue, NVL(e.containerid,e.enginecontainerid) containerid,
 e.containername,mfg.mfgordername,mfg.mfgorderid,pb.productname,p.description, decode(e.containerid,null,'未生成','已生成') TaskBillStatus
 ,decode(WF.WORKFLOWTYPE,'施工工艺','临时','技通工艺','临时','正常') WORKFLOWTYPE,e.containerno togetherflag,
 to_char(e.plannedstartdate,'yyyy-MM-dd HH:mm') plannedstartdate, to_char(e.plannedfinishdate,'yyyy-MM-dd HH:mm') plannedfinishdate
 ,b.bomid,e.workflowstepid,e.workflowid,wfs.workflowstepname,wfs.sequence
 FROM enginepartplan e 
 INNER JOIN workflowstep wfs ON e.workflowstepid=wfs.workflowstepid 
 INNER JOIN mfgorder mfg ON mfg.mfgorderid=e.mfgorderid AND mfg.productid != e.productid
 LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
 LEFT JOIN product p ON e.productid=p.productid
 LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
 LEFT JOIN workflow wf ON wf.workflowid=e.workflowid
 LEFT JOIN workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid
 LEFT JOIN (SELECT COUNT(b.Bomid) countnum,wm_concat(bt.billtypename) billtypename,
                wm_concat(DECODE(bt.billtypename,'大小件目录',b.bomid,NULL)) bomid,b.workflowid 
                from BOM b,billtype bt WHERE b.billtypeid=bt.billtypeid GROUP BY b.workflowid
                ) b ON b.workflowid=e.workflowid
                      </string>)

        sb.AppendLine(" WHERE e.containerno is not null AND e.usecompleted='0' ")
        If para.ContainsKey("HasBom") Then
            If para("HasBom") = "1" Then
                sb.AppendLine(" AND b.bomid >'0' and INSTR(b.billtypename,'大小件目录')>0 ")
            Else
                sb.AppendLine(" and (INSTR(b.billtypename,'大小件目录')=0 OR nvl(b.billtypename ,'0')='0')")
            End If
            'Else
            '    sb.AppendLine(" AND b.bomid >'0' and INSTR(b.billtypename,'大小件目录')>0 ")
        End If
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and e.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("PartType") Then
            If Not String.IsNullOrWhiteSpace(para("PartType")) Then
                sb.AppendFormat(" and pm.parttype='{0}' ", para("PartType"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
            End If
        End If

        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrdername"))
            End If
        End If

        If para.ContainsKey("WorkFlowType") Then
            If Not String.IsNullOrWhiteSpace(para("WorkFlowType")) Then
                sb.AppendFormat(" and decode(WF.WORKFLOWTYPE,'施工工艺','0','技通工艺','0','1')='{0}' ", para("WorkFlowType"))
            End If
        End If

        If para.ContainsKey("Status") Then
            If para("Status") = "0" Then
                sb.AppendLine(" AND decode(e.containerid,null,'未生成','已生成')='未生成' ")
            ElseIf para("Status") = "1" Then
                sb.AppendLine(" AND decode(e.containerid,null,'未生成','已生成')='已生成' ")
            Else

            End If
        End If

        'sb.AppendLine("GROUP BY u.attributevalue,im.containerno,im.containerid,")
        'sb.AppendLine(" pb.productname,p.description,wfs.workflowstepname,wfs.workflowid,ep.containerid,mfg.mfgordername,mfg.PLANNEDCOMPLETIONDATE,MFG.PlannedStartDate,WF.WORKFLOWTYPE,wf.description  ")

        sb.AppendLine("Order by e.plannedfinishdate,wfs.sequence,mfg.mfgordername,pb.productname,e.containername ASC")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
End Class
